package com.simp.utils;

import com.simp.config.DbConfig;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author pan.shun
 * @version 2.0
 * @since 2022/3/24 21:30
 */
@Component
@Slf4j
public class DbUtils {

    @Autowired
    private DbConfig dbConfig;

    /**
     * 根据表名获取ddl语句
     */
    public String getDDL(String tableName) {
        String sql = String.format("SHOW CREATE TABLE %s", tableName);
        Connection connection = null;
        PreparedStatement ps = null;
        String result = "";
        try {
            connection = getConnection();
            ps = connection.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            if (resultSet.next()) {
                result = resultSet.getString(2);
            }
            return result;
        } catch (SQLException e) {
            log.error("【查询表名的DDL语句】 执行异常: {}", ExceptionUtils.print(e));
            return null;
        } finally {
            closeConn(ps, connection);
        }
    }

    /**
     * 获取所有表名
     */
    public List<String> getTableNameList() {
        List<String> tableNameList = new ArrayList<>(16);
        String sql = "show tables";//查询sql
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = getConnection();
            ps = connection.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                tableNameList.add(resultSet.getString(1));
            }
            return tableNameList;
        } catch (SQLException e) {
            log.error("【获取所有表名】SQL执行异常: {}", ExceptionUtils.print(e));
            return null;
        } finally {
            closeConn(ps, connection);
        }
    }

    /**
     * 建立连接
     */
    private Connection getConnection() {
        Connection connection;
        try {
            Class.forName(dbConfig.getDriverClassName());
            connection = DriverManager.getConnection(
                    dbConfig.getUrl(),
                    dbConfig.getUserName(),
                    dbConfig.getPassword()
            );
        } catch (ClassNotFoundException | SQLException e) {
            log.error("连接MySQL数据库失败", e);
            return null;
        }
        return connection;
    }

    /**
     * 关闭连接
     */
    private void closeConn(PreparedStatement ps, Connection conn) {
        if (null != ps) {
            try {
                ps.close();
            } catch (SQLException e) {
                log.error("连接关闭异常", e);
            }
        }
        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("连接关闭异常", e);
            }
        }
    }

}
